package DBLayer;
import ModelLayer.*;

import java.sql.*;
import java.util.ArrayList;

public class DBSal
{
	/**
	 * Initializes instance variables
	 */
	private Connection con;
	
	/** Creates connection to the database and creates an instance of DBSal*/
	public DBSal()
	{
		con = DbConnection1.getInstance().getDBcon();
	}

	/** getAllSal finds all Sale with
	  * @param retrieveAssociation (Sal does not have foreign keys from other classes)
	  * @return
	  */
	public ArrayList<Sal> getAllSal(boolean retrieveAssociation)
	{
		return miscWhere("", retrieveAssociation);
	}
	
	/** getsal finds a specific Sal with
	 * 
	 * @param salNr
	 * @param retrieveAssociation (Sal does not have foreign keys from other classes)
	 * @return
	 */
	public Sal getSal(byte salNr, boolean retrieveAssociation)
	{
		String wClause = " salNr = '" + salNr + "'";
		return singleWhere(wClause, retrieveAssociation);
	}
	
	/** insertForestilling creates a sal SQL query to be inserted into database with
	 * 
	 * @param sal
	 * @return
	 * @throws Exception (If Sal is not created)
	 */
	public int insertSal(Sal sal) throws Exception
	{
		int nextSalNr = GetMax.getMaxId("Select max (salNr) from Sal");
		nextSalNr = nextSalNr + 1;
		//System.out.println("next salNr = " + nextSalNr);
		
		int rc = -1;
		String query ="INSERT INTO Sal(salNr, antalPladser, is3d) VALUES('"+
		
				nextSalNr + "','" +
				sal.getAntalPladser() + "','" +
				sal.getIs3D() + "')";
				
		//System.out.println("insert : " + query);
		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}//end try
		catch(SQLException ex)
		{
			//System.out.println("Sal ikke oprettet");
			throw new Exception ("Sal is not inserted correct");
		}
		return(rc);
	}
	
	/** deletesal removes a Sal from database with
	 * 
	 * @param salNr
	 * @return
	 */
	public int deleteSal(int salNr)
	{
		int rc=-1;
		
		String query ="DELETE FROM sal WHERE salNr = '" + 
				salNr + "'";
		//System.out.println(query);
		try
		{ //delete from sal
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}//end try
		catch(Exception ex)
		{
			//System.out.println("Delete exception in Sal db: "+ex);
		}
		return(rc);
	}
	
	/** updateSal creates an update sal SQL query to update a sal in the database with
	 * 
	 * @param sal
	 * @return
	 */
	public int updateSal(Sal sal)
	{
		Sal salObj  = sal;
		int rc=-1;

		String query="UPDATE sal SET "+
				"antalPladser ='"+ salObj.getAntalPladser() + "', " +
				"is3D ='"+ salObj.getIs3D() + "' " +
				"WHERE salNr = '"+ salObj.getSalNr() + "'";
		//System.out.println("Update query:" + query);
		try
		{ // update person
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);

			stmt.close();
		}//end try
		catch(Exception ex)
		{
			//System.out.println("Update exception in person db: "+ex);
		}
		return(rc);
	}
	
	/** singleWhere is used when we only want to select one Sal
	 * 
	 * @param wClause
	 * @param retrieveAssociation (Sal does not have foreign keys from other classes)
	 * @return
	 */
	private Sal singleWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		Sal salObj = new Sal();
		
		String query = buildQuery(wClause);
		//System.out.println(query);
		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			
			if( results.next() )
			{
				salObj = buildSal(results);
				//associate is to be build
				stmt.close();
			}
			else
			{
				salObj = null;
			}
		}//end try
		catch(Exception e)
		{
			//System.out.println("Query exception: "+e);
		}
		return salObj;
	}
	
	/** mischWere is used whenever we want to select more than one Sal or all Sale
	 * 
	 * @param wClause
	 * @param retrieveAssociation (Sal does not have foreign keys from other classes)
	 * @return
	 */
	private ArrayList<Sal> miscWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		ArrayList<Sal> list = new ArrayList<Sal>();
		
		String query = buildQuery(wClause);
		
		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			
			while(results.next() )
			{Sal salObj = new Sal();
			salObj = buildSal(results);
			list.add(salObj);
			}//end while
			stmt.close();
		}// end try
		
		catch(Exception e) 
		{
			//System.out.println("Query exception - select: "+e);
			e.printStackTrace();
		}
		return list;
	}
	
	
	/**
	 * buildQuery uses wClause insert String list of the attributes in a Sal and builds the
	 * WHERE part of the query that is to be committed to the SQl database
	 * @param wClause 
	 * @return
	 */
	private String buildQuery(String wClause)
	{
		String query = "SELECT * FROM Sal";
		
		if(wClause.length()>0)
			query=query+" WHERE "+ wClause;
		return query;
	}
	
	/** buildForestilling creates a sal object with
	 * 
	 * @param results (used to bring attributes to salObj)
	 * @return
	 */
	private Sal buildSal(ResultSet results)
	{
		Sal salObj = new Sal();
		try
		{
			salObj.setSalNr(results.getByte("salNr"));
			salObj.setAntalPladser(results.getInt("antalPladser"));
			salObj.setIs3D(results.getBoolean("is3D"));
		}
		catch(Exception e)
		{
			//System.out.println("error in building the sal object");
		}
		return salObj;
	}		
}
